Day Three:
Transform
Datasets

140 min approx

Overview

Questions

  • What are Tidy data, why are they useful, and how to transform untidy data to tidy one?
  • How to select some variables/columns only?
  • How to filter rows/cases that match certain conditions?
  • How to modify (even create) the content of a (possibly new) variable?
  • How to handle factors effectively in R/Tidyverse?
  • How to handle dates and time in R/Tidyverse?
  • How to handle strings in R/Tidyverse?

Lesson Objectives

To be able to

  • Use pivot_*, separate, unite function from the tidyr package in the Tidyverse to reshape data into tidy one.
  • Select/filter columns/rows of tibbles (i.e., data frames).
  • Change content of variable programmatically, possibly using content from other variables.
  • perform basic factor data management.
  • convert textual date/time into date/time R objects
  • use simple regular expression and main str_* functions to manage strings

Data shape

Tidy data

Illustrations from the Openscapes blog Tidy Data for reproducibility, efficiency, and collaboration by Julia Lowndes and Allison Horst

Untidy data

Illustrations from the Openscapes blog Tidy Data for reproducibility, efficiency, and collaboration by Julia Lowndes and Allison Horst

Why tidy data

Illustrations from the Openscapes blog Tidy Data for reproducibility, efficiency, and collaboration by Julia Lowndes and Allison Horst

Tidy rules

There are three interrelated rules that make a dataset tidy:

  1. Each variable is a column; each column is a variable.
  2. Each observation is a row; each row is an observation.
  3. Each value is a cell; each cell is a single value.

Why untidy data

  • Data is often organized to facilitate some goal other than analysis. For example, it’s common for data to be structured to make data entry, not analysis, easy.

Example: tidyverse::billboard dataset.1

library(tidyverse)

billboard

Warning

  • information in column:
    • wk1-wk76 should be a single variable: the week.
    • cell values of wk1-wk76 should be a single variable: the rank.

Start Tidying - tidyr::pivot_longer

  • Data is often organized to facilitate some goal other than analysis. For example, it’s common for data to be structured to make data entry, not analysis, easy.
library(tidyverse)

billboard |> 
  pivot_longer(
    cols = starts_with("wk"),
    names_to = "week",
    values_to = "rank"
  )

Important

  • tidyr::pivot_longer convert your data in “longer” format
  • cols: select which variable should be pivoting
  • names_to: define the column hosting the cols colnames
  • values_to: define the column hosting the cols values

Warning

Many possibly uninformative missing information!

Start Tidying - tidyr::pivot_longer

  • Data is often organized to facilitate some goal other than analysis. For example, it’s common for data to be structured to make data entry, not analysis, easy.
library(tidyverse)

billboard |> 
  pivot_longer(
    cols = starts_with("wk"),
    names_to = "week",
    values_to = "rank",
    values_drop_na = TRUE
  )

Important

  • tidyr::pivot_longer convert your data in “longer” format
  • cols: select which variable should be pivoting
  • names_to: define the column hosting the cols colnames
  • values_to: define the column hosting the cols values
  • values_drop_na: decide if rows with missing information in values should be removed

Selectors 1

  • var1:var10: variables lying between var1 on the left and var10 on the right.

  • starts_with("a"): names that start with “a”.

  • ends_with("z"): names that end with “z”.

  • contains("b"): names that contain “b”.

  • matches("x.y"): names that match regular expression x.y. 2

  • num_range(x, 1:4): names following the pattern, x1, x2, …, x4.

  • all_of(vars)/any_of(vars): names stored in the character vector vars. all_of(vars) will error if the variables aren’t present; any_of(var) will match just the variables that exist.

  • everything(): all variables.

  • last_col(): furthest column on the right.

  • where(is.numeric): all variables where is.numeric() returns TRUE.

Tip

  • !selection: only variables that don’t match selection.

  • selection1 & selection2: only variables included in both selection1 and selection2.

  • selection1 | selection2: all variables that match either selection1 or selection2

Multiple variable in colnames

who2

Tip

In case of multiple variable in each colname, you can pivoting them maintaining the underling structure. This way you can separate them in a further second step…

who2 |> 
  pivot_longer(
    cols = !(country:year),
    names_to = "diagnosis_gender_age", 
    values_to = "count"
  )

Multiple variable in colnames

who2

Tip

In case of multiple variable in each colname, you can pivoting them maintaining the underling structure. This way you can separate them in a further second step using tidyr::separate.

who2 |> 
  pivot_longer(
    cols = !(country:year),
    names_to = "diagnosis_gender_age", 
    values_to = "count"
  ) |> 
  separate(
    col = diagnosis_gender_age,
    into = c("diagnosis", "gender", "age"),
    sep = "_"
  )

Multiple variable in colnames

who2 |> 
  pivot_longer(
    cols = !(country:year),
    names_to = "diagnosis_gender_age", 
    values_to = "count"
  ) |> 
  separate(
    col = diagnosis_gender_age,
    into = c("diagnosis", "gender", "age"),
    sep = "_"
  )
who2 |> 
  pivot_longer(
    cols = !(country:year),
    names_to = c("diagnosis", "gender", "age"), 
    names_sep = "_",
    values_to = "count"
  )

Tip

You can also separate colnames containing multiple variables, and matching a regular pattern, into multiple variable in a single step.

My turn

YOU: Connect to our pad (https://bit.ly/ubep-rws-pad) and write there questions & doubts (and if I am too slow or too fast)

ME: Connect to the Day-3 project in RStudio cloud (https://bit.ly/ubep-rws-rstudio): script 11-pivoting.R

Your turn

Your turn

…and:

  1. Answer in the pad, with an “x” next to the correct answers. What are the main option for pivot_longer?
  • names_from
  • names_to
  • values_from
  • values_to
  1. Then, open the script 10-pivot_longer.R and follow the instruction step by step.
05:00

Important

To transform a table to a longer one, you need to put some of its columns names_to a new column, and their corresponding values_to another one! Possibly allowing values_drop_na.

tidyr::pivot_wider

Image from Data Carpentry’s R for Social Scientists

Reverse pivot - tidyr::pivot_wider

Animation of tidyverse verbs by Garrick Aden-Buie

Reverse pivot - example

library(tidyverse)
library(janitor)

bb_pivoted_twice <- billboard |> 
  pivot_longer(
    cols = starts_with("wk"),
    names_to = "week",
    values_to = "rank"
  ) |>
  pivot_wider(
    names_from = "week",
    values_from = "rank" 
  )

all.equal(
  billboard |> remove_empty("cols"),
  bb_pivoted_twice |> remove_empty("cols")
)
[1] TRUE

My turn

YOU: Connect to our pad (https://bit.ly/ubep-rws-pad) and write there questions & doubts (and if I am too slow or too fast)

ME: Connect to the Day-3 project in RStudio cloud (https://bit.ly/ubep-rws-rstudio): script 11-pivoting.R

Your turn

Your turn

…and:

  1. Answer in the pad, with an “x” next to the correct answers. What are the main option for pivot_wider?
  • names_from
  • names_to
  • values_from
  • values_to
  1. Then, open the script 11-pivot_wider.R and follow the instruction step by step.
05:00

Important

To transform a table to a wider one, you need to take new column names_from an existing column, and their corresponding values_from the associated one! Possibly with created missing values_filled.

Data management

dplyr - intro

Common structure:

  • The first argument is always a data frame
  • The subsequent arguments typically describe which columns to operate on, using the variable names (without quotes).
  • The output is always a new data frame.

Tip

All verbs in Tidyverse are designed to do one thing mainly, and to it well! So, to solve complex problem we will often combine multiple verbs, and we use the pipe (|>) as we are already familiar!

Rows - dplyr::filter

Important

dplyr::filter allows you to keep rows based on the values of the columns.

library(tidyverse)
library(here)
library(rio)

db <- here("data-raw", "Copenhagen_clean.xlsx") |> 
  import(setclass = "tibble")

db |> 
  filter(age < 18)

Rows - conditions

We can use any kind of condition inside dplyr::filter; e.g.,

And

db |> 
  filter((age < 18) & case)

Tip

If a variable is already a logical one, you can use it directly as it is as a condition! E.g.

db |> 
  filter(case) ## instead of case == TRUE

db |> 
  filter(!case) ## instead of case == FALSE

Rows - conditions

We can use any kind of condition inside dplyr::filter; e.g.,

Or

db |> 
  filter(gastrosymptoms | ate_anything)

Rows - conditions

We can use any kind of condition inside dplyr::filter; e.g.,

In

db |> 
  filter(age %in% 19:25)

Rows - conditions

We can use any kind of condition inside dplyr::filter; e.g.,

Not equal

db |> 
  filter(group != "student")

Rows - multiple conditions

We can also combine together multiple condition of arbitrary complexity at once

db |> 
  filter(!((age < 18) & case))

Tip

It could be difficult to remind the priority order of logical operators. Using parentheses to group each conditions is a safe way to not be wrong!

My turn

YOU: Connect to our pad (https://bit.ly/ubep-rws-pad) and write there questions & doubts (and if I am too slow or too fast)

ME: Connect to the Day-3 project in RStudio cloud (https://bit.ly/ubep-rws-rstudio): script 12-filter-and-select.R

Your turn

Your turn

…and:

  1. Imagine to have imported a db with a variable age, and you want to keep rows with age equal to 18 or 21. Before to evaluate it, does the following code return what you need? Answer in the pad, under the section 3.2. Ex20.
library(tidyverse)

db |>
  filter(age == 18 | 21)
  1. Then, open the script 12-filter.R, and follow the instruction step by step.
02:00

Important

Important

  • you can put arbitrary complex conditions returning logical vectors of the same length of the number of rows of the data frame, involving any column of the data frame in use also.

Columns - dplyr::select

For analyses, you do not need to remove columns from your dataset, but it could be extremely useful to see more clearly only the data you need to see time to time.1

You can select the column to keep using the dplyr::select() verb providing:

The variables you like to keep

library(tidyverse)

db |> 
  select(sex, age, case)

Columns - dplyr::select

For analyses, you do not need to remove columns from your dataset, but it could be extremely useful to see more clearly only the data you need to see time to time.1

You can select the column to keep using the dplyr::select() verb providing:

A range of variables you like to keep

library(tidyverse)

db |> 
  select(sex:class)

Columns - dplyr::select

For analyses, you do not need to remove columns from your dataset, but it could be extremely useful to see more clearly only the data you need to see time to time.1

You can select the column to keep using the dplyr::select() verb providing:

Excludig the selection (!)

library(tidyverse)

db |> 
  select(!diarrhoea:jointpain)

Columns - dplyr::select

For analyses, you do not need to remove columns from your dataset, but it could be extremely useful to see more clearly only the data you need to see time to time.1

You can select the column to keep using the dplyr::select() verb providing:

Matching a condition - where 

library(tidyverse)

db |> 
  select(where(is.logical))

Selectors 1

  • var1:var10: variables lying between var1 on the left and var10 on the right.

  • starts_with("a"): names that start with “a”.

  • ends_with("z"): names that end with “z”.

  • contains("b"): names that contain “b”.

  • matches("x.y"): names that match regular expression x.y. 2

  • num_range(x, 1:4): names following the pattern, x1, x2, …, x4.

  • all_of(vars)/any_of(vars): names stored in the character vector vars. all_of(vars) will error if the variables aren’t present; any_of(var) will match just the variables that exist.

  • everything(): all variables.

  • last_col(): furthest column on the right.

  • where(is.numeric): all variables where is.numeric() returns TRUE.

Tip

  • !selection: only variables that don’t match selection.

  • selection1 & selection2: only variables included in both selection1 and selection2.

  • selection1 | selection2: all variables that match either selection1 or selection2

My turn

YOU: Connect to our pad (https://bit.ly/ubep-rws-pad) and write there questions & doubts (and if I am too slow or too fast)

ME: Connect to the Day-3 project in RStudio cloud (https://bit.ly/ubep-rws-rstudio): script 12-filter-and-select.R

Your turn

Your turn

…and:

  1. Before to evaluate it, in the pad, under the section 3.2. Ex21, write (in a new line) all the possible ways you can imagine to select the variable sex, age, group using dplyr::select from our data frame db imported from Copenhagen_clean.xlsx .

  2. What do you expect the following code will return (including an error):

db |>
  select(any_of(c("age", "foo")))
  1. Then, open the script 13-select.R and follow the instruction step by step.
05:00

Important

  • all_of(vec) is for strict selection. If any of the variables in the character vec is missing, an error is thrown.
  • any_of(vec) doesn’t check for missing variables. It is especially useful with negative selections, when you would like to make sure a variable is removed.

Mutate

We can also add new columns which are calculated from existing ones.

We can use simple algebra

library(tidyverse)

db |> 
  # select just to return few results
  select(id, incubation) |> 
  mutate(
    incubation_days = incubation / 24
  )

Mutate

We can also add new columns which are calculated from existing ones.

We can use functions on variables

library(tidyverse)

db |> 
  # select just to return few results
  select(id, incubation) |> 
  mutate(
    incubation_norm = (
      incubation - mean(incubation, na.rm = TRUE)
    ) / sd(incubation, na.rm = TRUE) 
  )

Mutate

We can also add new columns which are calculated from existing ones.

We can use variables just created

library(tidyverse)

db |> 
  # select just to return few results
  select(id, age, group, class, case) |> 
  mutate(
    adult = (age > 18) & (
      (group != "student") |
      is.na(class)
    ),
    adult_case = adult & case
  )

Mutate

We can also add new columns which are calculated from existing ones.

Pay attention on vectorized Vs. summary functions

library(tidyverse)

sample_df <- tibble(
  x = c(1, 5, 7),
  y = c(3, 2, NA)
)

sample_df |> 
  mutate(
    # rows element-wise
    min_vec = pmin(x, y, na.rm = TRUE),
    max_vec = pmax(x, y, na.rm = TRUE),
    # cols global
    min_all = min(x, y, na.rm = TRUE),
    max_all = max(x, y, na.rm = TRUE),
  )

Warning

  • Summary functions (e.g., min, max):
    • Takes: vectors.
    • Returns: a single value.
  • Vectorized functions (e.g., pmin, pmax):
    • Takes: vectors.
    • Returns: vectors (the same length as the input).

Conditional mutates - Binary: dplyr::if_else

To mutate a variable accordingly to a binary condition

library(tidyverse)

db |> 
  mutate(
    age_class = if_else(
      age >= 18,
      "adult",
      "child"
    )
  ) |> 
  select(age, age_class)

Important

dplyr::if_else requires compatible types in the output.

Conditional mutates - Subsequent: dplyr::case_when

To mutate a variable accordingly to multiple subsequent conditions

library(tidyverse)

db |> 
  mutate(
    age_class = case_when(
      age >  24 ~ "adult (prof)",
      age >= 18 ~ "adult (stud)",
      age >= 15 ~ "young (stud)",
      TRUE      ~ "child"
    )
  ) |> 
  select(age, age_class)

Important

dplyr::case_when takes condition ~ value pairs. condition must be a logical vector; when it’s TRUE, the valule will be used.

  • If none of the cases match, the output gets an NA.
  • Conditions are considered in order, so you should put the most specific case first!
  • TRUE ~ <dafault_value> is used to specify the “default”/catch all value.

Grouping and summarizing

We can group rows into groups meaningful for your analysis by one or more variables, and then summarize each group into a single row performing a summary operation.

library(tidyverse)

db |> 
  group_by(class) |> 
  summarize(
    mean_age = age |> 
      mean(na.rm = TRUE),
    
    n = n(),
    
    n_teachers = sum(
      group == "teacher",
      na.rm = TRUE
    )
  )

Counts

If we want to count the number of rows in each group, we can use simply dplyr::count instead of dplyr::group_by and dplyr::summarize.

db |> 
  count(class)
db |> 
  count(class, group)

My turn

YOU: Connect to our pad (https://bit.ly/ubep-rws-pad) and write there questions & doubts (and if I am too slow or too fast)

ME: Connect to the Day-3 project in RStudio cloud (https://bit.ly/ubep-rws-rstudio): script 13-transforming.R

Your turn

Your turn

…and:

  1. Before to try it, in the pad, under the section 3.2. Ex22 write your guess respect the output of using dplyr::mutate assigning the same name of an already existing variable. E.g.
library(tidyverse)

db |> 
  mutate(
  age = age * 365.25
)
  1. Then, open the script 14-mutate.R and follow the instruction step by step.
05:00

Important

As all the other verbs in the Tidyverse, dplyr::mutate

  • It takes a data frame in input, always.
  • It returns a data frame in output, always.
  • It doesn’t change it’s input, never.

Homework

Posit’s RStudio Cloud Workspace

Instructions

  • Go to: https://bit.ly/ubep-rws-rstudio

Your turn

  • Project: day-3
  • Instructions:
    • Go to: https://bit.ly/ubep-rws-website
    • The text is the Day-3 assessment under the tab “Summative Assessments”.
    • (on RStudio Cloud) homework/day_three-summative.html
  • Script to complete: homework/solution.R

Acknowledgment

To create the current lesson, we explored, used, and adapted content from the following resources:

The slides are made using Posit’s Quarto open-source scientific and technical publishing system powered in R by Yihui Xie’s Kintr.

Additional resources

License

This work by Corrado Lanera, Ileana Baldi, and Dario Gregori is licensed under CC BY 4.0

References

UBEP’s R training for supervisors